package cn.stylefeng.guns.modular.transport.service.impl;

import java.io.*;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import javax.xml.ws.Action;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.IdUtil;
import cn.stylefeng.guns.modular.hx.order.utils.JdbcUtls;
import cn.stylefeng.guns.modular.location.excel.ExcelUtils;
import cn.stylefeng.guns.modular.transport.model.result.SjCntVo;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import com.fasterxml.jackson.databind.annotation.JsonAppend;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.modular.basic.model.params.BasicPrivatecarParam;
import cn.stylefeng.guns.modular.basic.model.result.BasicPrivatecarResult;
import cn.stylefeng.guns.modular.basic.service.BasicPrivatecarService;
import cn.stylefeng.guns.modular.transport.entity.Hxchengyunshanganalysis;
import cn.stylefeng.guns.modular.transport.entity.TDispatch;
import cn.stylefeng.guns.modular.transport.mapper.TDispatchMapper;
import cn.stylefeng.guns.modular.transport.model.params.TDispatchParam;
import cn.stylefeng.guns.modular.transport.model.params.TransportReceiptParam;
import cn.stylefeng.guns.modular.transport.model.result.TDispatchResult;
import cn.stylefeng.guns.modular.transport.model.result.TransportReceiptResult;
import cn.stylefeng.guns.modular.transport.service.TDispatchService;
import cn.stylefeng.guns.modular.transport.service.TransportReceiptService;
import cn.stylefeng.guns.sys.modular.system.entity.User;
import cn.stylefeng.guns.sys.modular.system.service.UserService;
import cn.stylefeng.roses.core.util.ToolUtil;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

/**
 * 调度单表 服务实现类
 */
@Service
public class TDispatchServiceImpl extends ServiceImpl<TDispatchMapper, TDispatch> implements TDispatchService {

    @Resource
    private UserService userService;

    @Resource
    private TransportReceiptService transportReceiptService;

    @Resource
    private BasicPrivatecarService basicPrivatecarService;

    @Autowired
    private DataSource dataSource;

    @Value("${hik.baseUrl}")
    private String hikBaseUrl;


    @Value("${file.tmpImpPath}")
    private String tmpImpPath;

    @Override
    public void add(TDispatchParam param) {
        TDispatch entity = getEntity(param);
        this.save(entity);
    }

    @Override
    public void delete(TDispatchParam param) {
        this.removeById(getKey(param));
    }

    @Override
    public void update(TDispatchParam param) {
        TDispatch oldEntity = getOldEntity(param);
        TDispatch newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public TDispatchResult findBySpec(TDispatchParam param, Long operateId) {
        return this.baseMapper.findBySpec(param, operateId);
    }

    @Override
    public TDispatchResult findByDispatchNumber(String dispatchNumber) {
        return this.baseMapper.findByDispatchNumber(dispatchNumber);
    }

    @Override
    public List<TDispatchResult> findListBySpec(TDispatchParam param) {
        return this.baseMapper.findListBySpec(param);
    }

    @Autowired
    private TDispatchService tDispatchService;

    @Override
    public LayuiPageInfo findPageBySpec(TDispatchParam param, String condition, String driver1, Long operateId) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param, condition, driver1, operateId);
        List<TDispatchResult> list = page.getRecords();
        for (TDispatchResult tDispatchResult : list) {
            if (!"PENDING_REPORT".equals(tDispatchResult.getStateTb())) {
                TDispatchParam tDispatchParam = new TDispatchParam();
                Integer aa = tDispatchResult.getId();
                tDispatchParam.setId(aa);
                tDispatchService.update2(tDispatchParam);
            }
        }
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public void update2(TDispatchParam tDispatchParam) {
        this.baseMapper.update2(tDispatchParam);
    }

    @Autowired
    private TDispatchMapper tDispatchMapper;

    @Override
    public ResponseData update5(TDispatchParam tDispatchParam) {
        int updatedCount = tDispatchMapper.update5(tDispatchParam);
        return ResponseData.success(updatedCount);
    }

    @Override
    public LayuiPageInfo findPageBySpecPaymentSettlement(TDispatchParam param, String condition, String driver1, Long operateId) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.findPageBySpecPaymentSettlement(pageContext, param, condition, driver1, operateId);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo findPageBySpecPaymentConfirm(TDispatchParam param, String condition, String driver1, Long operateId) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.findPageBySpecPaymentConfirm(pageContext, param, condition, driver1, operateId);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo findexaminePageBySpec(TDispatchParam param, String condition, String driver1, Long operateId) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.findexaminePageBySpec(pageContext, param, condition, driver1, operateId);
        return LayuiPageFactory.createPageInfo(page);
    }

    private Serializable getKey(TDispatchParam param) {
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private TDispatch getOldEntity(TDispatchParam param) {
        return this.getById(getKey(param));
    }

    private TDispatch getEntity(TDispatchParam param) {
        TDispatch entity = new TDispatch();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }

    @Override
    public TDispatch findByThelastone() {
        return this.baseMapper.findByThelastone();
    }

    /**
     * 校验调度单 是否全部回传
     */
    @Override
    public void checkComplete(String dispatchNumber) {
        User user = userService.getcurrentUser();
        Long deliverId = user.getDeliverId();
        Long deptId = user.getDeptId();
        TDispatchParam param = new TDispatchParam();
        param.setDispatchNumber(dispatchNumber);
        param.setDeliverId(deliverId);
        param.setDeptId(deptId);
        param.setTstate("DISPATCHED");
        TDispatchResult result = this.findBySpec(param, user.getOperateId());//查询该车 已发运调度单
        if (result != null) {
            TransportReceiptParam param2 = new TransportReceiptParam();
            param2.setDispatchNumber(result.getDispatchNumber());
            param2.setTstate("NOT_SYNCED");
            param2.setDeliverId(deliverId);
            param2.setDeptId(deptId);
            List<TransportReceiptResult> list = transportReceiptService.findListBySpec(param2, user.getOperateId());//查询调度单 未回传地址
            if (list.size() == 0) {
                result.setTstate("DELIVERED");
                ToolUtil.copyProperties(result, param);
                this.update(param);//更新调度单 已送达

                BasicPrivatecarParam param3 = new BasicPrivatecarParam();
                param3.setDeliverId(deliverId);
                param3.setPrivatecarCarnumber(result.getCarnumber());
                BasicPrivatecarResult result2 = basicPrivatecarService.findBySpec(param3, deptId, user.getOperateId());
                result2.setTooDeployvolume("0");
                result2.setPrivatecarState("IDLE");
                ToolUtil.copyProperties(result2, param3);
                basicPrivatecarService.update(param3);
            }
        }
    }

    @Override
    public Integer sumDayFaYunCarCount(TDispatchParam tDispatchParam) {
        return this.baseMapper.sumDayFaYunCarCount(tDispatchParam);
    }

    @Override
    public Integer sumMonthFaYunCarCount(TDispatchParam tDispatchParam) {
        return this.baseMapper.sumMonthFaYunCarCount(tDispatchParam);
    }

    @Override
    public List<TDispatchResult> findchengyunshangcount(TDispatchParam param, String condition, String driver1, Long operateId) {
        return this.baseMapper.findchengyunshangcount(param, condition, driver1, operateId);
    }

    @Override
    public Integer countCarnumber(TDispatchParam param, Long operateId) {
        return this.baseMapper.countCarnumber(param, operateId);
    }

    @Override
    public TDispatchResult findBySpecAndCar(TDispatchParam param) {
        return this.baseMapper.findBySpecAndCar(param);
    }

    @Override
    public List<SjCntVo> getSJCnt() {
        return this.baseMapper.getSJCnt();
    }

    @Override
    public TDispatchResult findByYunshuCar(TDispatchParam param, Long operateId) {
        return this.baseMapper.findByYunshuCar(param, operateId);
    }

    @Override
    public TDispatchResult findsumMonthFaYunCarCount(TDispatchParam tDispatchParam, Long operateId) {
        return this.baseMapper.findsumMonthFaYunCarCount(tDispatchParam, operateId);
    }

    @Override
    public TDispatchResult sumfulllvBySpec(TDispatchParam tDispatchParam) {
        return this.baseMapper.sumfulllvBySpec(tDispatchParam);
    }

    @Override
    public List<TDispatchResult> findHxtaizhang(TDispatchParam tDispatchParam, String condition, Long operateId) {
        return this.baseMapper.findHxtaizhang(tDispatchParam, condition, operateId);
    }

    @Override
    public List<TDispatchResult> findHxzhaungzaicount(TDispatchParam tDispatchParam, String condition, Long operateId) {
        return this.baseMapper.findHxzhaungzaicount(tDispatchParam, condition, operateId);
    }

    @Override
    public List<Hxchengyunshanganalysis> findHxchengyunshanganalysis(TDispatchParam tDispatchParam, String condition,
                                                                     Long operateId) {
        return this.baseMapper.findHxchengyunshanganalysis(tDispatchParam, condition, operateId);
    }

    @Override
    public Integer countcheci(TDispatchParam countcheci) {
        return this.baseMapper.countcheci(countcheci);
    }

    @Override
    public List<TDispatchResult> findHxfayuncheci(TDispatchParam tDispatchParam, String condition, Long operateId) {
        return this.baseMapper.findHxfayuncheci(tDispatchParam, condition, operateId);
    }

    @Override
    public List<TDispatchResult> findshifouyipeizhiByspec(TDispatchParam tDispatchParam, Long operateId) {
        return this.baseMapper.findshifouyipeizhiByspec(tDispatchParam, operateId);
    }

    @Override
    public List<TDispatchResult> findHxzhuangchejindu(TDispatchParam tDispatchParam, String condition, Long operateId) {
        return this.baseMapper.findHxzhuangchejindu(tDispatchParam, condition, operateId);
    }


	/**
	 * 上传附件并检验
	 * @param file
	 * @param operateId
	 * @param user
	 * @param response
	 * @return
	 * @throws IOException
	 */
    @Override
    public ResponseData getExceldata(MultipartFile file, Long operateId, User user, HttpServletResponse response) throws IOException {
        ResponseData data = ResponseData.success();
        if (ToolUtil.isEmpty(operateId)) {
            data.setSuccess(false);
            data.setMessage("Please select the associated project");
            return data;
        }

        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            data.setMessage("Incorrect file format for upload");
            data.setSuccess(false);
        }

        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Connection conn = null;
        PreparedStatement psInsertTmpImp = null;
        PreparedStatement psUpdate = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Sheet sheet = wb.getSheetAt(0);
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            conn.prepareStatement(this.getDropTmpTabSql()).execute();
            conn.prepareStatement(this.getCreateTmpTabSql()).execute();
            psInsertTmpImp = conn.prepareStatement(this.getInsertTmpTabSql());
            psUpdate = conn.prepareStatement(this.getUpdateDispathchSql());
            int addrow = 0;
            for (int r = 1; r <= sheet.getLastRowNum() + 1; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                String dispatchNumber = ExcelUtils.getCellValue(row.getCell(0));
                if (ToolUtil.isNotEmpty(dispatchNumber)) {
                    psInsertTmpImp.setInt(1, r);
                    psInsertTmpImp.setString(2, dispatchNumber);
                } else {
                    continue;
                }
                String freightMoney = ExcelUtils.getCellValue(row.getCell(1));
                if (ToolUtil.isNotEmpty(freightMoney)) {
                    psInsertTmpImp.setBigDecimal(3, new BigDecimal(freightMoney));
                } else {
                    psInsertTmpImp.setBigDecimal(3, new BigDecimal(0));
                }
                psInsertTmpImp.addBatch();
                addrow++;
                if (addrow > 2000) {
                    psInsertTmpImp.executeBatch();
                    addrow = 0;
                }
            }
            if (addrow > 0) {
                psInsertTmpImp.executeBatch();
            }
            boolean flag = true;
            //查询对比结果
			ps = conn.prepareStatement(this.getRsSql());
            ps.setLong(1,operateId);
			rs = ps.executeQuery();
			Set<Long> ids = new HashSet<>();
			while(rs.next()){
                Row row = sheet.getRow(rs.getInt(1));
                if(row.getCell(2) == null){
                    row.createCell(2);
                }
                if(row.getCell(3) == null){
                    row.createCell(3);
                }
                if(ToolUtil.isEmpty(rs.getString("id"))){
                    row.getCell(3).setCellValue("Failed. Shipping order number does not exist.");
                    flag = false;
                    data.setCode(555);
                    continue;
                }
                row.getCell(2).setCellValue(rs.getString("freight_money"));
				BigDecimal freightMoneyRs = rs.getBigDecimal("freight_money_rs");
				if("PENDING_REPORT".equals(rs.getString("state_tb"))){
                    row.getCell(3).setCellValue("Failed. Duplicate submission.");
                    flag = false;
                    data.setCode(555);
					continue;
				}
				if(freightMoneyRs.compareTo(new BigDecimal(1)) == 1){
                    row.getCell(3).setCellValue("Failed. The difference exceeds 1.");
                    flag = false;
                    data.setCode(555);
				}else{
                    ids.add(rs.getLong("id"));
                    row.getCell(3).setCellValue("Success");
				}
			}
			//修改状态
			if(flag && ids.size() > 0){
                for (Long id : ids){
                    psUpdate.setLong(1,id);
                    psUpdate.addBatch();
                }
                psUpdate.executeBatch();
            }
			//如果不存在 创建目录
			if(!FileUtil.exist(this.tmpImpPath)){
                FileUtil.mkdir(this.tmpImpPath);
            }
			String saveFileName = IdUtil.fastSimpleUUID()+".xlsx";
			data.setData(saveFileName);
            String path = this.tmpImpPath+"/"+ saveFileName;
            // 保存工作簿到磁盘
            try (FileOutputStream out = new FileOutputStream(path)) {
                wb.write(out);
            } catch (IOException e) {
                e.printStackTrace();
            }
			conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            data.setSuccess(false);
            data.setMessage("Data import exception. Please modify and try again.");
            JdbcUtls.rollback(conn);
        } finally {
            JdbcUtls.closePreparedStatement(psUpdate);
            JdbcUtls.closePreparedStatement(psInsertTmpImp);
            JdbcUtls.closeAll(rs, ps, conn);
        }
        return data;
    }
    //;

    private String getDropTmpTabSql() {
        StringBuffer sql = new StringBuffer();
        sql.append("DROP TEMPORARY TABLE IF EXISTS TMP_IMP ");
        return sql.toString();
    }

    /**
     * 获取创建临时表sql
     *
     * @return
     */
    private String getCreateTmpTabSql() {
        StringBuffer sql = new StringBuffer();
        sql.append("create temporary table TMP_IMP (");
        sql.append("idx int ");
        sql.append(",dispatch_number varchar(32) ");
        sql.append(",freight_money decimal(18,3) ");
        sql.append(") ");
        return sql.toString();
    }

    /**
     * 获取插入临时表语句
     *
     * @return
     */
    private String getInsertTmpTabSql() {
        StringBuffer sql = new StringBuffer();
        sql.append("insert into TMP_IMP (");
        sql.append("idx");
        sql.append(",dispatch_number");
        sql.append(",freight_money");
        sql.append(") values(?,?,?) ");
        return sql.toString();
    }

	/**
	 * 获取对比结果
	 * @return
	 */
	private String getRsSql() {
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		sql.append("t1.idx ");
		sql.append(",t2.dispatch_number ");
        sql.append(",t1.freight_money AS freight_money_r ");
		sql.append(",t2.freight_money ");
		sql.append(",ABS(t1.freight_money  - IFNULL(t2.freight_money,0)) as freight_money_rs ");
		sql.append(",t2.state_tb ");
        sql.append(",t2.id ");
		sql.append("from TMP_IMP t1 ");
		sql.append("left join t_dispatch t2 ");
		sql.append("on t1.dispatch_number = t2.dispatch_number ");
		sql.append("and t2.operate_id = ? ");
		return sql.toString();
	}


    /**
     * 修改提报状态
     * @return
     */
    private String getUpdateDispathchSql() {
        StringBuffer sql = new StringBuffer();
        sql.append("update t_dispatch ");
        sql.append("set state_tb = 'PENDING_REPORT' ");
        sql.append(",state_tb_time = CURRENT_TIMESTAMP ");
        sql.append("where id = ? ");
        return sql.toString();
    }



}